package com.eryos.android.cigarettecounter.database;

/**
 * Project :  CigaretteCounter
 * Author :   Olivier Combe 
 * URL :      http://eryos.fr/CigaretteCounter/
 *
 * Copyright (C) 2011 - Olivier Combe 
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

import java.math.BigDecimal;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;

public class DBAdapter 
{ 
    private static final String TAG = "DBAdapter";
    
    private static final String DATABASE_NAME = "eryos.db";
    protected static final String DATABASE_TABLE = "smoke_table";
    
    private static final int DATABASE_VERSION = 8;
    
    public static final String KEY_ROWID = "_id";
    public static final String KEY_SMOKE_TIME = "smoke_time";
    public static final String KEY_SMOKE_REASON = "smoke_reason";
    public static final String KEY_SMOKE_PRICE  = "smoke_valid";
    
	private String[] fields_list = new String[] {
        		KEY_ROWID, 
        		KEY_SMOKE_TIME,
        		KEY_SMOKE_REASON,
        		KEY_SMOKE_PRICE
    };

    private static final String DATABASE_CREATE =
        "CREATE TABLE "+DATABASE_TABLE+" ( "
	        + KEY_ROWID 	   + " INTEGER PRIMARY KEY AUTOINCREMENT, "
	        + KEY_SMOKE_TIME   + " DATETIME DEFAULT CURRENT_TIMESTAMP, "
	        + KEY_SMOKE_REASON + " TEXT,"
	        + KEY_SMOKE_PRICE  + " TEXT"
        + ");";
        
    protected final Context context;  
    
	protected DatabaseHelper DBHelper;
    protected SQLiteDatabase db;

    public DBAdapter(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
        open();
    }
    
   //---opens the database---
    public DBAdapter open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() {
        DBHelper.close();        
    }
    
    public void insertCurrentTime(String _reason, float price) {   
    	if ( price < 0 )
    		price = 0;
    	db.execSQL("INSERT INTO "+DATABASE_TABLE+" VALUES ( null, datetime(), '"+_reason+"', '"+price+"');");        
    }
    
    public void deleteAll() {   
    	System.out.println("DELETE ALL DATA ....");
    	db.execSQL("DELETE FROM "+DATABASE_TABLE+";");        
    }
    
    //---updates a title---
    public boolean update(long rowId, String _time,  String _reason, boolean _valid) {
        ContentValues args = new ContentValues();
        
        String valid = "Y";
        if ( ! _valid ) 
        	valid = "N";
        	
        args.put(KEY_SMOKE_TIME, _time);
        args.put(KEY_SMOKE_REASON, _reason);
        args.put(KEY_SMOKE_PRICE, valid);
        
        return db.update(DATABASE_TABLE, args, 
                         KEY_ROWID + "=" + rowId, null) > 0;
    }

    
    
    /****************************************************************************************/
    /****************************************************************************************/
    /****************************************************************************************/
    
    public void loadInitialData(){
    }
    
    /**
     * Return the number of cigarettes smoked today 
     */
    public int countCigaretteToday(){
     	int res = -1;

     	Cursor mCursor = db.rawQuery(
     			"SELECT count(1) as NB " +
     			"FROM "+DATABASE_TABLE+" " +
     			"WHERE date(smoke_time) = date('now');", null);
     	
         if (mCursor != null) {
         	mCursor.moveToFirst();
             res = mCursor.getInt(0);
         }
     	
     	return res;
     }
    
     /**
      * Count the number of days registered in the DB
      */
      public int countNbDays(){
      	int res = -1;
      	
      	String[] columns = new String[]{"count(distinct date(smoke_time)) as NB"};
      	String selection = null; 
      	String[] selectionArgs = null;
      	String groupBy = null;
      	String having = null;
      	String orderBy = null;
      	String limit = null;
      	Cursor mCursor = db.query(false, DATABASE_TABLE, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
      	
          if (mCursor != null) {
          	mCursor.moveToFirst();
              res = mCursor.getInt(0);
          }
      	
      	return res;
      }
     
     
      /**
       * Return the number of cigarettes smoked today 
       */
       public String getLastCigarette(){
       	String res = "NA";
       	
       	String[] columns = new String[]{"max(smoke_time) as LAST"};
       	String selection = null; 
       	String[] selectionArgs = null;
       	String groupBy = null;
       	String having = null;
       	String orderBy = null;
       	String limit = null;
       	Cursor mCursor = db.query(false, DATABASE_TABLE, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
       	
           if (mCursor != null) {
           	mCursor.moveToFirst();
               res = mCursor.getString(0);
           }
       	
       	return res;
       }
       
       /**
        * Return the number of cigarettes smoked today 
        */
        public String getFirstCigarette(){
        	String res = "NA";
        	
        	String[] columns = new String[]{"min(smoke_time) as FIRST"};
        	String selection = "smoke_time is not null and smoke_time > 0"; 
        	String[] selectionArgs = null;
        	String groupBy = null;
        	String having = null;
        	String orderBy = null;
        	String limit = null;
        	Cursor mCursor = db.query(false, DATABASE_TABLE, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
        	
            if (mCursor != null) {
            	mCursor.moveToFirst();
                res = mCursor.getString(0);
            }
        	
        	return res;
        }

    /**
    * Return the number of cigarettes smoked this week 
    */
    public int countCigaretteThisWeek(){
    	int res = -1;
    	
    	String[] columns = new String[]{"count(1) as NB"};
    	String selection = "strftime('%W', smoke_time) = strftime('%W','now')";
    	String[] selectionArgs = null;
    	String groupBy = null;
    	String having = null;
    	String orderBy = null;
    	String limit = null;
    	
    	Cursor mCursor = db.query(false, DATABASE_TABLE, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    	
        if (mCursor != null) {
            mCursor.moveToFirst();
            res = mCursor.getInt(0);
        }
    	
    	return res;
    }
    
    /**
    * Return the number of cigarettes smoked this month 
    */
    public int countCigaretteThisMonth(){
    	int res = -1;
    	
    	String[] columns = new String[]{"count(1) as NB"};
    	String selection = "strftime('%m', smoke_time) = strftime('%m','now')";
    	String[] selectionArgs = null;
    	String groupBy = null;
    	String having = null;
    	String orderBy = null;
    	String limit = null;
    	
    	Cursor mCursor = db.query(false, DATABASE_TABLE, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    	
        if (mCursor != null) {
            mCursor.moveToFirst();
            res = mCursor.getInt(0);
        }
    	
    	return res;
    }
    
    /**
    * Return the average number of cigarettes smoked by day this month
    */
    public double averageCigarettesMonthly(){
    	double res = -1;
    	
    	String sqlQuery = 
    		"SELECT sum(nb), count(jour)" +
			"FROM (" +
				"SELECT strftime('%m', smoke_time) Mois, strftime('%d/%m/%Y', smoke_time) Jour, count(1) Nb "+
				"FROM "+DATABASE_TABLE+" " +
				"WHERE strftime('%m', smoke_time) = strftime('%m','now') "+
				"GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time) "+
				"ORDER BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time)" +
			") "+
			"GROUP BY Mois "+
			"ORDER BY Mois;";			
  
    	Cursor mCursor = db.rawQuery(sqlQuery, null);
    	
        if (mCursor != null) {
            mCursor.moveToFirst();
            
            BigDecimal sum = new BigDecimal(mCursor.getDouble(0)).setScale(2);
            BigDecimal nb = new BigDecimal(mCursor.getDouble(1)).setScale(2);
            
            res = sum.divide(nb).doubleValue();
        }
    	
    	return res;
    }
    
    /**
    * Return the average number of cigarettes smoked by day this week
    */
    public double averageCigarettesWeekly(){
    	double res = -1;
    	
    	String sqlQuery = 
    		"SELECT sum(nb), count(jour) " +
			"FROM ( " +
				"SELECT strftime('%W%', smoke_time) Semaine, strftime('%d/%m/%Y', smoke_time) Jour, count(1) Nb " +
				"FROM "+DATABASE_TABLE+" "+
				"WHERE strftime('%W', smoke_time) = strftime('%W','now') "+
				"GROUP BY strftime('%W%', smoke_time), strftime('%d/%m/%Y', smoke_time) " +
				"ORDER BY strftime('%W%', smoke_time), strftime('%d/%m/%Y', smoke_time) " +
			") " +
			"GROUP BY semaine " +
			"ORDER BY semaine;";
  
    	Cursor mCursor = db.rawQuery(sqlQuery, null);
    	
        if (mCursor != null) {
            mCursor.moveToFirst();
            
            BigDecimal sum = new BigDecimal(mCursor.getDouble(0)).setScale(2);
            BigDecimal nb = new BigDecimal(mCursor.getDouble(1)).setScale(2);
            
            res = sum.divide(nb).doubleValue();
        }
    	
    	return res;
    }
    
    /**
     * Return the history of cigarettes smoked this month
     */
     public ArrayList<SmokeCounter> cigarettesHistoryCountMonthly(){
     	ArrayList<SmokeCounter> res = new ArrayList<SmokeCounter>();
     	
     	String sqlQuery = 
     		"SELECT strftime('%d/%m/%Y', smoke_time) Jour, count(1) Nb " +
 			"FROM "+DATABASE_TABLE+" "+
 			"WHERE strftime('%m', smoke_time) = strftime('%m','now') "+
 			"GROUP BY strftime('%d/%m/%Y', smoke_time) " +
 			"ORDER BY strftime('%d/%m/%Y', smoke_time);";
   
     	Cursor mCursor = db.rawQuery(sqlQuery, null);
     	
 		mCursor.moveToFirst();
 		
 		for(int i=0; i < mCursor.getCount(); i++) {	
 			res.add(new SmokeCounter(mCursor.getString(0),mCursor.getInt(1)));
 			mCursor.moveToNext();		
 		} 
     	
     	return res;
     }
     
     /**
      * Return the history of cigarettes smoked this month
      */
      public ArrayList<SmokeAverage> cigarettesHistoryAverageMonthly(){
      	ArrayList<SmokeAverage> res = new ArrayList<SmokeAverage>();
      	
      	String sqlQuery = 
  		    "SELECT strftime('%d/%m/%Y', smoke_time) Jour, count(1) Nb "+
  			"FROM "+DATABASE_TABLE+" "+
  			"WHERE strftime('%m', smoke_time) = strftime('%m','now') "+
  			"GROUP strftime('%d/%m/%Y', smoke_time) "+
  			"ORDER strftime('%d/%m/%Y', smoke_time);";
    
      	Cursor mCursor = db.rawQuery(sqlQuery, null);
      	
  		mCursor.moveToFirst();
  		
  		for(int i=0; i < mCursor.getCount(); i++) {		
  			res.add(new SmokeAverage(mCursor.getString(0),mCursor.getDouble(1)));
  			mCursor.moveToNext();		
  		} 
      	
      	return res;
      }
    
    /**
     * Return the history of cigarettes smoked this week
     */
     public ArrayList<SmokeCounter> cigarettesHistoryCountWeekly(){
     	ArrayList<SmokeCounter> res = new ArrayList<SmokeCounter>();
     	
     	String sqlQuery = 
     		"SELECT strftime('%d/%m/%Y', smoke_time) Jour, count(1) Nb " +
 			"FROM "+DATABASE_TABLE+" "+
 			"WHERE strftime('%W', smoke_time) = strftime('%W','now') "+
 			"GROUP BY strftime('%d/%m/%Y', smoke_time) " +
 			"ORDER BY strftime('%d/%m/%Y', smoke_time);";
   
 		Cursor mCursor = db.rawQuery(sqlQuery, null);
     	
 		mCursor.moveToFirst();
 		
 		for(int i=0; i < mCursor.getCount(); i++) {		
 			res.add(new SmokeCounter(mCursor.getString(0),mCursor.getInt(1)));
 			mCursor.moveToNext();		
 		} 
     	
     	return res;

     }
     
     /**
      * Return the history of average smoked cigarettes for last 6 weeks
      */
      public ArrayList<SmokeAverage> cigarettesHistoryAverageWeekly(){
      	ArrayList<SmokeAverage> res = new ArrayList<SmokeAverage>();
      	
      	String sqlQuery = 
  			"SELECT strftime('%d/%m/%Y', smoke_time) Jour, count(1) Nb " +
  			"FROM "+DATABASE_TABLE+" "+
  			"WHERE strftime('%W', smoke_time) = strftime('%W','now') "+
  			"GROUP BY strftime('%d/%m/%Y', smoke_time) " +
  			"ORDER BY strftime('%d/%m/%Y', smoke_time);";
    
  		Cursor mCursor = db.rawQuery(sqlQuery, null);
      	
  		mCursor.moveToFirst();
  		
  		for(int i=0; i < mCursor.getCount(); i++) {		
  			res.add(new SmokeAverage(mCursor.getString(0),mCursor.getDouble(1)));
  			mCursor.moveToNext();		
  		} 
      	
      	return res;

      }
            
    /** ************************************************************************** **/
    /** ************************************************************************** **/
    /** ************************************************************************** **/    
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                  + " to "
                  + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
            onCreate(db);
        }
    }
    

    
}